package dao.impl;

import com.mysql.cj.util.StringUtils;
import dao.BaseDao;
import dao.StudentsDao;
import entity.Students;

import javax.naming.NamingException;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class StudentsDaoimpl extends BaseDao implements StudentsDao {

    @Override
    public int getTotalCount(String xh, String name) throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {

            StringBuilder sql = new StringBuilder("SELECT COUNT(*) FROM students WHERE 1=1");

            if (!StringUtils.isNullOrEmpty(xh)) {
                sql.append(" AND student_number LIKE ?");
            }
            if (!StringUtils.isNullOrEmpty(name)) {
                sql.append(" AND name LIKE ?");
            }

            ps = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            if (!StringUtils.isNullOrEmpty(xh)) {
                ps.setString(paramIndex++, "%" + xh + "%");
            }
            if (!StringUtils.isNullOrEmpty(name)) {
                ps.setString(paramIndex++, "%" + name + "%");
            }

            rs = ps.executeQuery();
            return rs.next() ? rs.getInt(1) : 0;
        } finally {
            closeResources(rs, ps, conn);
        }
    }

    @Override
    public List<Students> cx(String xh, String name, int page, int pageSize)
            throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Students> list = new ArrayList<>();

        try {

            StringBuilder sql = new StringBuilder(
                    "SELECT student_id, student_number, name, gender FROM students WHERE 1=1");

            if (!StringUtils.isNullOrEmpty(xh)) {
                sql.append(" AND student_number LIKE ?");
            }
            if (!StringUtils.isNullOrEmpty(name)) {
                sql.append(" AND name LIKE ?");
            }
            sql.append(" LIMIT ?, ?");

            ps = conn.prepareStatement(sql.toString());
            int paramIndex = 1;
            if (!StringUtils.isNullOrEmpty(xh)) {
                ps.setString(paramIndex++, "%" + xh + "%");
            }
            if (!StringUtils.isNullOrEmpty(name)) {
                ps.setString(paramIndex++, "%" + name + "%");
            }

            int offsetPage=page*pageSize;
            ps.setInt(paramIndex++, offsetPage);
            ps.setInt(paramIndex, pageSize);

            rs = ps.executeQuery();
            while (rs.next()) {
                Students stu = new Students();
                stu.setStudent_id(rs.getInt("student_id"));
                stu.setStudent_number(rs.getString("student_number"));
                stu.setName(rs.getString("name"));
                stu.setGender(rs.getString("gender"));
                list.add(stu);
            }
            return list;
        } finally {
            closeResources(rs, ps, conn);
        }
    }

    @Override
    public int modify(String stu, String name, String gender, int id)
            throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement pt = null;

        try {

            String sql = "UPDATE students SET student_number=?, name=?, gender=? WHERE student_id=?";
            pt = conn.prepareStatement(sql);
            pt.setString(1, stu);
            pt.setString(2, name);
            pt.setString(3, gender);
            pt.setInt(4, id);
            return pt.executeUpdate();
        } finally {
            closeResources(null, pt, conn);
        }
    }

    @Override
    public int append(String stu, String name, String gender)
            throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement pt = null;

        try {

            String sql = "INSERT INTO students(student_number, name, gender) VALUES(?, ?, ?)";
            pt = conn.prepareStatement(sql);
            pt.setString(1, stu);
            pt.setString(2, name);
            pt.setString(3, gender);
            return pt.executeUpdate();
        } finally {
            closeResources(null, pt, conn);
        }
    }

    @Override
    public int delete(int id) throws SQLException, NamingException, ClassNotFoundException {
        getConnection();
        PreparedStatement pt = null;

        try {

            String sql = "DELETE FROM students WHERE student_id=?";
            pt = conn.prepareStatement(sql);
            pt.setInt(1, id);
            return pt.executeUpdate();
        } finally {
            closeResources(null, pt, conn);
        }
    }

    @Override
    public List<Students> cxcw() throws SQLException, ClassNotFoundException {
       getConnection();
        PreparedStatement pre = null;
        ResultSet ss = null;
        List<Students> list = new ArrayList<>();

        try {

            String sql = "SELECT student_id, student_number, name, gender FROM students " +
                    "WHERE student_id NOT IN (SELECT student_id FROM dorm_beds WHERE student_id IS NOT NULL)";
            pre = conn.prepareStatement(sql);
            ss = pre.executeQuery();

            while (ss.next()) {
                Students stu = new Students();
                stu.setStudent_id(ss.getInt("student_id"));
                stu.setStudent_number(ss.getString("student_number"));
                stu.setName(ss.getString("name"));
                stu.setGender(ss.getString("gender"));
                list.add(stu);
            }
            return list;
        } finally {
            closeResources(ss, pre, conn);
        }
    }

    // 辅助方法：关闭数据库资源
    private void closeResources(ResultSet rs, PreparedStatement stmt, Connection conn) {
        try {
            if (rs != null) rs.close();
        } catch (SQLException e) {
            System.err.println("关闭ResultSet失败: " + e.getMessage());
        }

        try {
            if (stmt != null) stmt.close();
        } catch (SQLException e) {
            System.err.println("关闭PreparedStatement失败: " + e.getMessage());
        }

        try {
            if (conn != null) conn.close();
        } catch (SQLException e) {
            System.err.println("关闭Connection失败: " + e.getMessage());
        }
    }
}